#coding=utf-8
#开发时间：2022/10/31  21:06
#开发人员：水云道人
from time import sleep

import pymysql
from createDataByInsert.excelUtils import ExcelUtils, strToList
import json
from utils.environments_json import json_data

# mysql_json = json_data.get(json_data.get("current_env")).get("mysql")
# server = mysql_json.get("server")
# port = int(mysql_json.get("port"))
# user = mysql_json.get("username")
# passwd = mysql_json.get("password")
# database = mysql_json.get("database")
# dbTest = pymysql.connect(host=server, port=port, user=user, password=passwd, db=database, charset='utf8',
#                      autocommit=True)
# cursorTest = dbTest.cursor()
#
#
# dbPro = pymysql.connect(host="124.70.217.1", port=3306, user="jx_test", password="juexiao@123", db="jxcpa", charset='utf8')
# cursorPro = dbPro.cursor()
#
# i=0
# while(True):
#     sql="select * from tms_paper_topic_normal order by id asc limit {},500;".format(i)
#     cursorPro.execute(sql)
#     redData=cursorPro.fetchall()
#     if redData is None or len(redData)==0:
#         break
#     else:
#         for data in redData:
#             sql="delete from tms_paper_topic_normal where id={}".format(data[0])
#             cursorTest.execute(sql)
#             sql = "INSERT INTO `tms_paper_topic_normal` (`id`, `paper_id`, `number`, `topic_id`, `type`, `create_time`, `update_time`) VALUES (%s,%s,%s,%s,%s,%s,%s);"
#             cursorTest.execute(sql,data)
#     sleep(3)
#     i+=500


if __name__ == '__main__':
    excelName1="C:/Users/22734/Desktop/hw_cpa/data_excel/CAP_api.xls"
    excelName2="C:/Users/22734/Desktop/hw_cpa/data_excel/cpa_api_sydr.xls"


def getCasesCount(*paths):
    S = set()
    for path in paths:
        eu = ExcelUtils(path, engine="xlrd")
        print(json.dumps(eu.ef.sheet_names))
        for sheet in eu.ef.sheet_names:
            datas=eu.converSheetData(sheet,engine="xlrd")
            for data in datas:
                l=strToList(str(data["uri"]))
                if l is not None and len(l)>0:
                    for d in l:
                        S.add(d)
                else:
                    try:
                        d=json.loads(data["uri"])
                        for key in d.keys():
                            S.add(d[key])
                    except:

                        S.add(data["uri"])
    print(S)
    for s in S:
        if "nan" == str(s):

            return len(S)-1#把nan减去
        else:
            return len(S)
print(getCasesCount(excelName1,excelName2))
